﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using OgilvyOne.Model;
using OgilvyOne.IDAL;

namespace OgilvyOne.SqlServerDAL
{
    public class LabelConfig : ILabelConfig
    {
        // Methods
        public void Delete(int configID)
        {
            string cmdText = "update lalel_config_info set status = '1' where configID = " + configID;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public IList getLabelList()
        {
            string cmdText = string.Empty;
            string str2 = "select configID, menu_parent_id,menu_id,sort,f.label_id,status,menu_name,label_name from lalel_config_info f , SYS_MENU s ,LABEL_INFO a where f.menu_id = s.menu_uid and f.label_id=a.label_id and status = '0'";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, str2, cmdParms))
            {
                while (reader.Read())
                {
                    LabelConfigInfo info = new LabelConfigInfo();
                    info.ConfigID = reader.GetInt32(0);
                    info.MenuParentId = reader.GetInt32(1);
                    info.MenudId = reader.GetInt32(2);
                    info.Sort = reader.GetInt32(3);
                    info.LabelId = reader.GetInt32(4);
                    info.Status = reader.GetString(5);
                    info.Menu_name = reader.GetString(6);
                    info.Label_name = reader.GetString(7);
                    cmdText = "select menu_name from SYS_MENU where menu_uid=" + info.MenuParentId;
                    using (SqlDataReader reader2 = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
                    {
                        if (reader2.Read())
                        {
                            info.MenuParent_name = reader2.GetString(0);
                        }
                    }
                    list.Add(info);
                }
            }
            return list;
        }

        public LabelConfigInfo getLabelList(int configID)
        {
            SqlDataReader reader;
            string cmdText = "select configID, menu_parent_id,menu_id,sort,f.label_id,status,menu_name,label_name from lalel_config_info f , SYS_MENU s ,LABEL_INFO a where f.menu_id = s.menu_uid and f.label_id=a.label_id and status = '0' and configID = " + configID;
            LabelConfigInfo info = null;
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    info = new LabelConfigInfo();
                    info.ConfigID = reader.GetInt32(0);
                    info.MenuParentId = reader.GetInt32(1);
                    info.MenudId = reader.GetInt32(2);
                    info.Sort = reader.GetInt32(3);
                    info.LabelId = reader.GetInt32(4);
                    info.Status = reader.GetString(5);
                    info.Menu_name = reader.GetString(6);
                    info.Label_name = reader.GetString(7);
                }
            }
            string str2 = "select menu_name from SYS_MENU where menu_uid=" + info.MenuParentId;
            using (reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, str2, cmdParms))
            {
                while (reader.Read())
                {
                    info.MenuParent_name = reader.GetString(0);
                }
            }
            return info;
        }

        public void Insert(LabelConfigInfo labelinfo)
        {
            string cmdText = string.Concat(new object[] { "insert into lalel_config_info(menu_parent_id,menu_id,sort,label_id,status) values(", labelinfo.MenuParentId, ",", labelinfo.MenudId, ",", labelinfo.Sort, ",", labelinfo.LabelId, ",'", labelinfo.Status, "')" });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void Update(LabelConfigInfo labelinfo)
        {
            string cmdText = string.Concat(new object[] { "update lalel_config_info set menu_parent_id = ", labelinfo.MenuParentId, ",menu_id =", labelinfo.MenudId, ",sort =", labelinfo.Sort, ",label_id =", labelinfo.LabelId, ",status = '", labelinfo.Status, "' where configID =", labelinfo.ConfigID });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }
    }
}